package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.JTable;

import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;

public class CustomQueries {

	private static final String DATABASE_URL = "jdbc:mysql://localhost/MovieBase";
	private static final String USERNAME = "moviebase";
	private static final String PASSWORD = "moviebase";

	private Connection connection = null;
	private PreparedStatement customQuery;
	private JTable table;

	public CustomQueries() {
		try {
			connection = DriverManager.getConnection(DATABASE_URL, USERNAME,
					PASSWORD);

		} catch (SQLException e) {
			System.out.println(e);
		}
	}

	public JTable getTable(String query) {

		ResultSet result;
		try {
			customQuery = connection.prepareStatement(query);

			result = customQuery.executeQuery();

			ResultSetMetaData md = result.getMetaData();
			int columnCount = md.getColumnCount();

			Vector columns = new Vector(columnCount);

			// store column names
			for (int i = 1; i <= columnCount; i++)
				columns.add(md.getColumnName(i));

			Vector data = new Vector();
			Vector row;

			// store row data
			while (result.next()) {
				row = new Vector(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					row.add(result.getString(i));
				}
				data.add(row);
			}

			table = new JTable(data, columns);
			return table;

		} catch (SQLException e) {
			System.out.println("Error in CustomQueries");
			table = new JTable();
			return table;
		} 
	}
}
